<?php 

dump("Starting to import<OL>");

extract($_POST);

//
// Open Database connection
dump("opening DB connection");
$link = mysql_connect('localhost', 'root', 'root');
if (!$link) {
    die('Could not connect: ' . mysql_error());
} else {
  $rolesettings = mysql_select_db("RoleSettings", $link);
  
  if (!$rolesettings) {
      die ('Can\'t use foo : ' . mysql_error());
  } else {
    //
    // This function links into a chain of other functions to do all the work
    get_data_from_files($link,"Prod");
    get_data_from_files($link,"Test");
    get_data_from_files($link,"Dev");
  } 
}
//
// Close DB
dump("Closing DB connection, All finished.");
mysql_close($link);

//////////////
//
// Supporting Functions
//
////////////
function drop_old_values($role, $link, $site) {
  $sql = "DELETE FROM `Roles` WHERE `Role`='$role' AND `Site`='$site'";
  dump("Emptied all ".$role." data from ".$site);
  return mysql_query($sql, $link);
}

//
// Read in each of the csv files, then call the insert function for each data set. 
function get_data_from_files($link, $site) {
  //
  // Open Directory 
  // Look in ./csv for files ending in .csv
  if ($handle = opendir('./csv/'.$site)) {
      while (false !== ($file = readdir($handle))) {
          if ( strstr($file,".txt") ) {
              //
              // Which role/time are we loking at?
              list(,$current_role,$end) = explode('_',$file);
              list($created) = explode('\.',$end);
			
              dump("Parsing: ".$current_role." - ".$created);
              //
              // Empty the Current role table
              drop_old_values($current_role, $link, $site);
              //
              // For each file (Open, Parse, Insert)
              $contents = file("./csv/".$site."/".$file);
              //
              // Chew up the first line, then capture: Tool,Permission,OU Type,Value
              // For each line after than, ignoring the 5, 6, 7 fields
              // i.e.  "Accelerator","See Course Design Accelerator","Organization","-1" ( ,"380000","380002","1" )
              $contents = array_slice($contents, 1);
              foreach($contents as $key => $line) {
                list($tool, $permission, $outype, $value) = str_getcsv($line, ',', '"');
                
                $csv[$current_role][$created][$key]["tool"] = $tool;
                $csv[$current_role][$created][$key]["permission"] = $permission; 
                $csv[$current_role][$created][$key]["outype"] = $outype; 
                $csv[$current_role][$created][$key]["value"] = $value; 
              } //close foreach
            //
            // write to Database
            write_to_db($csv, $link, $site);
            unset($csv);
			
          } // close if
      } // close while
      closedir($handle);
  } //close if
}

//
// Traverse the Array and add values to DB
function write_to_db($csv, $link, $site) {
  //
  // Role
  foreach($csv as $role => $set) {
    dump("<LI>INSERTing new records for: ".$role);
    // 
    // Created
    foreach($set as $created => $subset) {
      //
      // Index
      foreach ($subset as $key => $data) {
        //
        // Value
        extract($data);        
        $sql = sprintf("INSERT INTO `RoleSettings`.`Roles` (`Site`, `Role`, `Tool`, `Permission`, `OUType`, `Value`, `Created`) VALUES ('%s','%s','%s','%s','%s','%s','%s');", mysql_real_escape_string($site), mysql_real_escape_string($role), mysql_real_escape_string($tool), mysql_real_escape_string($permission), mysql_real_escape_string($outype), mysql_real_escape_string($value), mysql_real_escape_string($created) );  

         $result = mysql_query($sql, $link);
        
        // Check result
        // This shows the actual query sent to MySQL, and the error. Useful for debugging.
        if (!$result) {
            $message  = 'Invalid query: ' . mysql_error() . "\n";
            $message .= 'Whole query: ' . $sql;
            die($message);
        }       
      } // data
    } // created
  } //role
}



function dump($var) {
  echo "<pre>";
  print_r($var);
  echo "</pre>\n";
  flush();
}

?>
